Medicaid Drug Rebate Program (MDRP) Database

options(future.globals.maxSize = 2 * 1024 * 10E6);

.cran_libs <- c("purrr", "jsonlite", "httr", "summarytools", "munsell", "cachem"
                , "SmartEDA", "htmltools", "slider", "stringi", "magrittr", "plotly"
                , "DT", "data.table", "pdftools", "lubridate", "future", "furrr", "future.callr")
.git_libs <- paste0("book.of.", c("utilities", "features", "workflow")) |> c("architect")
              
if (!"book.of.workflow" %in% dir(sprintf("%s/library", R.home()))){
  if (!"remotes" %in% dir(sprintf("%s/library", R.home()))){ 
    htmltools::tags$span(style = "text-deoration:italic; color:#333333; ", "... installing missing package 'remotes'")
    install.packages("remotes", repos = "https://cloud.r-project.org") 
  }
  
  htmltools::tags$span(
    style = "text-deoration:italic; color:#333333; "
    , "... installing missing packages 'book.of.utilities', 'book.of.features', 'book.of.workflow', 'architect'"
    );
  
  remotes::install_github("delriaan/book.of.utilities", subdir = "pkg")
  remotes::install_github("delriaan/book.of.features", subdir = "pkg")
  remotes::install_github("delriaan/book.of.workflow", subdir = "pkg")
  remotes::install_github("delriaan/architect", subdir = "pkg")
}

library(book.of.workflow)
load_unloaded(!!!.cran_libs, autoinstall = TRUE)
load_unloaded(!!!.git_libs)

urls <- list(
  data = list(
    `Data Dictionary` = "https://www.medicaid.gov/medicaid-chip-program-information/by-topics/prescription-drugs/downloads/recordspecficationanddefinitions.pdf"
    , Data = "https://download.medicaid.gov/data/drugproducts1q_2023.csv"
    ) |> imap(\(x, y) tags$li(tags$a(href = x, y))) |> tags$ol()
  , git_libs = map(.git_libs, \(x) tags$li(tags$a(title = paste0("delriaan/", x), x))) |> list() |> tags$ul()
  , openFDA = "https://download.open.fda.gov/drug/ndc/drug-ndc-0001-of-0001.json.zip"
  )

.cache <- cache_disk(dir = "r_session_cache")

Purpose &
Metadata

This project explores the Medicaid Drug Rebate Program (MDRP) database via API calls (dataset description here):

1. Data Dictionary
2. Data

Required LIbraries

CRAN		GitHub
purrr jsonlite httr summarytools munsell	cachem SmartEDA htmltools slider stringi magrittr DT data.table pdftools lubridate future furrr future.callr	book.of.utilities book.of.features book.of.workflow architect

Data
Wrangling

Retrieve Data

# MDRP database data
if (rlang::is_empty(dir(params$data, pattern = "api_data[.][Rr]data$"))){
  if (!"api_data" %in% ls()){ 
    api_data <- urls$data |> 
      as.character() |> 
      stri_extract_all_regex("http.+csv", simplify = TRUE) |> 
      as.vector() %T>% 
      (\(x) tags$p(sprintf("Retrieve data from '%s'", x)) |> print())() |>
      GET() |>
      content() |>
      rawToChar() |>
      (\(x) read.csv(text = x, ))() |>
      as.data.table(na.rm = FALSE) |> 
      modify_at(c(4:7), as.character) %>% 
      modify_at(ls(., pattern = "Date"), lubridate::mdy) %>% 
      setnames(stri_replace_all_fixed(names(.) |> tolower(), ".", "_"))
  }
  
  if (!"api_dictionary" %in% ls()){ 
    api_dictionary <- invisible(urls$data |> 
      as.character() |> 
      stri_extract_all_regex("http.+pdf", simplify = TRUE) |> 
      as.vector() |>
      GET() |>
      content() |> 
      pdf_text())
    
    .summary_labels <- names(api_data) |> 
        rlang::set_names() |>
        map_chr(\(x) stri_replace_all_fixed(x, ".", " ", vectorize_all = FALSE)) |>
        imap_chr(\(x, y){ 
          api_dictionary |> 
            stri_extract_all_regex(
              sprintf(
                fmt = "(%s)[:]\n.+"
                , stri_replace_all_fixed(
                    x
                    , c("Pkg"
                        , "Intro"
                        , "COD Status"
                        , "FDA Application Number"
                        , "FDA Therapeutic Equivalence Code"
                        )
                    , c("Package"
                        , "Intro."
                        , "Covered Outpatient Drug [(]COD[)] Status"
                        , "FDA Application Number/OTC Monograph Number"
                        , "TEC"
                        )
                    , vectorize_all = FALSE
                    )
                )
              , simplify = TRUE
              ) |>
            stats::na.omit() |>
            as.vector() |>
            discard(\(x) x == "") %>%
            (\(i){ 
              .out <- ifelse(identical(character(), i), y, paste(i, collapse = "\n"))
              ifelse(stri_length(.out) > 50, paste0(.out, " ..."), .out)
            })()
        });
    
    iwalk(.summary_labels, \(x, y){ 
      .label = x; 
      api_data <<- modify_at(api_data, y, \(i){ attr(i, "label") <- .label; i }) 
    })
  }
} else { 
  if (!"api_data" %in% ls()){ 
    dir(params$data, pattern = "api_data[.][Rr]data$") |> load() 
  }
}

Retrieve data from 'https://download.medicaid.gov/data/drugproducts1q_2023.csv'

# openFDA supplementary data
if (rlang::is_empty(dir(params$data, pattern = "openFDA_ndc[.][Rr]data$"))){
  if (!"openFDA_ndc" %in% ls()){ openFDA_ndc <- (\(x){ 
      json.file <- "drug-ndc-0001-of-0001.json";
    
      if (!json.file %in% dir()){ 
        GET(urls$openFDA, write_disk(x, TRUE)) 
        unzip(zipfile = x)
      }
      
      read_json(path = json.file);
    })("ndc_json.zip") |> (\(x){
      data.table::rbindlist(modify_at(x$results, 1:length(x$results), as.data.table), fill = TRUE) |>
        setattr("metadata", x$meta)
    })()
  }
} else { 
  if (!"openFDA_ndc" %in% ls()){ 
    dir(params$data, pattern = "openFDA_ndc") |> load() 
  }
}

Prepare Data

NDC sequences come in a various formats, usually a 4-4-x, 5-4-x, or 5-3-x sequence (each integer indicating string length). Sometimes other formats arise, so normalizing all NDC sequences is a good idea, especially when there is a desire (or need) to join different data containing intersecting NDCs.

The following shows the NDC sequences in the OpenFDA and MDRP data:

I’ll create a function to check NDC formats and conform them in order to join the two data sets using a conformed NDC sequence:

check_ndc_format <- \(lc, pc){ 
  pc <- modify_if(pc, \(i) stri_length(i) < 3, \(i) stri_pad_left(i, width = 3, pad = "0"))
  lc <- modify_if(
          lc
          , \(i) stri_length(i) < 4
          , \(i) stri_pad_left(i, width = ifelse(stri_length(pc) == 3, 5, 4), pad = "0")
          )
  paste(lc, pc, sep = "-")
}

if (rlang::is_empty(dir(params$data, pattern = "master_drug_data[.][Rr]data$"))){
  if (!"master_drug_data" %in% ls()){
    master_drug_data <- (\(x, i) x[
                    i, on = "alt_ndc==product_ndc"
                    , nomatch = 0
                    , allow.cartesian = TRUE
                    , `:=`(pharm_class = pharm_class
                           , dea_schedule = dea_schedule
                           , product_type = product_type
                           , route = route
                           , marketing_category = marketing_category
                           )
                    , by = .EACHI
                    ])(api_data[, alt_ndc := map2_chr(labeler_code, product_code, check_ndc_format)]
                      , openFDA_ndc)
  
    master_drug_data[, `:=`(
      pharm_class := map_chr(pharm_class, \(x) unlist(x) %||% "~")
      , route := map_chr(route, \(x) unlist(x) %||% "~")
      )]
  }
} else {
  if (!"master_drug_data" %in% ls()){ 
    dir(params$data, pattern = "master_drug_data[.][Rr]data$", full.names = TRUE) |> load()
  }
}

Temporal
Analysis

master_drug_data is a great dataset for constructing simple, time-based metrics. Given the natural order of the types of events, it is easy to setup event sequence metrics using package lubridate

if (rlang::is_empty(dir(pattern = "ndc_events[.][Rr]data$"))){ 
  if (!"ndc_events" %in% ls()){ 
    ndc_events <- define(
      master_drug_data
      , ~alt_ndc + fda_product_name + drug_category + 
          pharm_class + dea_schedule + product_type + 
          route + marketing_category + fda_approval_date + 
          market_date + termination_date + reactivation_date
      , unique(.SD)
      , days_to_market = duration(as.integer(market_date) - as.integer(fda_approval_date), units = "days")
      , on_market_age = duration({ 
            ifelse(is.na(termination_date), ifelse(is.na(reactivation_date), today(), reactivation_date), termination_date) - 
            ifelse(is.na(reactivation_date), ifelse(is.na(termination_date), market_date, termination_date), reactivation_date)
          }, units = "days")
      , days_market_absent = duration({ 
            ifelse(is.na(reactivation_date), today(), reactivation_date) - 
            ifelse(is.na(termination_date), today(), termination_date)
          }, units = "days")
      );
    
    rlang::set_names(
      c("Days between approval \nand market release"
          , "Days active on market"
          , "Days most-recently \nabsent from market")
      , c("days_to_market", "on_market_age", "days_market_absent")
      )|> 
      iwalk(\(x, y) rlang::parse_expr(sprintf("setattr(ndc_events$%s, \"label\", \"%s\")", y, x)) |> eval(envir = .GlobalEnv))
  }
} else {
  if (!"ndc_events" %in% ls()){ 
    dir(pattern = "ndc_events[.][Rr]data$", full.names = TRUE) |> load()
  }
}